package com.bookShop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bookShop.dao.SmallTypeDao;
import com.bookShop.entity.SmallType;
import com.bookShop.tool.DBConnection;

public class SmallTypeDaoImpl implements SmallTypeDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	public int delete(int id) {
		// TODO Auto-generated method stub
		int isdelete = 0;
		String sql = "delete from smalltype where id = ?";
		conn = DBConnection.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			isdelete = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return isdelete;
	}

	public int insert(SmallType bigType) {
		// TODO Auto-generated method stub
		int isinsert = 0;
		String sql = "insert into smalltype values (null,?,?)";
		conn = DBConnection.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, bigType.getBigTypeId());
			ps.setString(2, bigType.getName());
			isinsert = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return isinsert;
	}

	public int update(SmallType bigType) {
		// TODO Auto-generated method stub
		int isupdate = 0;
		String sql = "update smalltype set bigTypeId = ?,name = ? where id = ?";
		conn = DBConnection.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, bigType.getBigTypeId());
			ps.setString(2, bigType.getName());
			ps.setInt(3, bigType.getId());
			isupdate = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return isupdate;
	}

	public List<SmallType> selectAll() {
		// TODO Auto-generated method stub
		String sql = "select * from smalltype order by bigTypeId";
		conn = DBConnection.getConnection();
		List<SmallType> smalltypes = new ArrayList<SmallType>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				SmallType smalltype = new SmallType();
				smalltype.setId(rs.getInt("id"));
				smalltype.setName(rs.getString("name"));
				smalltype.setBigTypeId(rs.getInt("bigTypeId"));
				smalltypes.add(smalltype);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return smalltypes;
	}

	public List<SmallType> selectByBigTypeId(int bigTypeId) {
		// TODO Auto-generated method stub
		String sql = "select * from smalltype where bigTypeId = ?";
		conn = DBConnection.getConnection();
		List<SmallType> smalltypes = new ArrayList<SmallType>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, bigTypeId);
			rs = ps.executeQuery();
			while (rs.next()) {
				SmallType smalltype = new SmallType();
				smalltype.setId(rs.getInt("id"));
				smalltype.setName(rs.getString("name"));
				smalltype.setBigTypeId(rs.getInt("bigTypeId"));
				smalltypes.add(smalltype);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return smalltypes;
	}

	public SmallType selectById(int id) {
		// TODO Auto-generated method stub
		String sql = "select * from smalltype where id = ?";
		SmallType smalltype = new SmallType();
		conn = DBConnection.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				smalltype.setId(rs.getInt("id"));
				smalltype.setName(rs.getString("name"));
				smalltype.setBigTypeId(rs.getInt("bigTypeId"));
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return smalltype;
	}

}
